Copyright: Copyright (c) 1997 Borland International, Inc. All rights reserved.


Contents

Introduction

What the Wizard Can Do

What the Wizard Can't Do

How to use this Wizard

One-To-Many Reports


Introduction

Welcome to the Visual dBASE 7 Report Converter Wizard

The Visual dBASE 7 Report Converter Wizard will help you convert Crystal Reports 3.0 report and label files created in dBASE 5.0 for Windows and Visual dBASE 5.5 to the new Visual dBASE 7 Report classes.

The Visual dBASE Report classes, including PageTemplate, StreamFrame, StreamSource, Group and Text (among others) provide a powerful object-oriented solution to complex reporting requirements.

However, these reusable report components are considerably different in both approach and execution from the Crystal binary .RPT and .RPL files. Which means that certain commonly-used conventions of the old style reports and labels will not convert easily to the new classes. Conversely, many of the powerful new features of the Visual dBASE 7 Report classes were not available in Crystal 3.0 and, hence, are not present in your old Crystal files to be converted.

To make matters even more complex, the Report classes depend on the new Data classes (Data Module, Session, Database and Query) and SQL SELECT statements to provide their data stream. There are significant enhancements and limitations to the data classes as well that make one-to-one conversions difficult. UDF expression indexes, for example, are no longer supported. Filters and keys must be scoped to Queries and Rowsets instead of to Tables and Workareas. Set Fields is no longer meaningful.

Therefore, the Report Converter Wizard concentrates on moving the most labor-intensive parts of your Crystal reports and labels (layout, columns, bands, groups, headers and footers) to the new object-oriented syntax of Visual dBASE 7. It generates queries from your legacy .QBE and Crystal files, but may not be able to bring over complex relations. On the last page of this Wizard and above the header of the generated .REP and .LAB files, you’ll find a “problem” list indicating the items that the Wizard could not successfully convert.

To derive the greatest benefit from the Visual dBASE 7 Report Converter Wizard, you should familiarize yourself with what the Wizard can do and what the Wizard cannot do. In addition, you may want to adjust the generated reports to take advantage of the subtleties of the new data classes and report classes.


What the Wizard Can Do
What the Wizard Can Do.

The Visual dBASE 7 Report Converter Wizard can:

Generate Visual dBASE 7 .REP report files from Crystal 3.0 binary .RPT files.

Generate Visual dBASE 7 .LAB label files from Crystal 3.0 binary .RPL files.

Create Visual dBASE 7 Query objects from Visual dBASE .QBE files.

Convert Header and Title Text.

Convert columns to Visual dBASE 7 Report Text controls.

Convert Crystal Groups to Visual dBASE 7 Group objects.

Convert Crystal Calculated Fields to Visual dBASE 7 Calculated Field objects.

Preserve the order in which your data is displayed or printed.

Preserve fonts, font sizes, attributes and colors

Preserve simple single-field relations (e.g. SET RELATION TO CustNo INTO Invoice)

Convert Aggregate Functions (Sum, Count, etc.)


What the Wizard Can't Do
What the Wizard Cannot Do

The Visual dBASE 7 Report Converter Wizard does not support the following:

Complex expression relations (e.g. SET RELATION TO UPPER(First_name)+UPPER(Last_name) INTO Address)

Initial Filter expressions (e.g. SET FILTER TO UPPER(Status) = ‘NEW’)

Initial Keys (e.g. SET KEY TO ‘NEW’)

Binary Objects (except for Memo fields, which are supported)

Correct order of data if the .QBE file cannot be successfully run from the Wizard.

OLE Objects (not supported in Visual dBASE 7 Report classes)

Note: If you have a report that contains an OLE object, the Report Converter Wizard will reject the entire report as an unsupported file format. Remove OLE objects before attempting to convert reports.

External references (variables, filters, relations or keys set in files other than the associated .QBE)

Empty .QBEs

Note: It has been a common practice in Visual dBASE 5.5 to open tables, establish filters, set keys and otherwise manipulate data in a calling file and then use an empty .QBE file attached to a Crystal report. The Report Converter Wizard uses table names extracted from the Crystal binary file, so Queries should come across intact even when using a dummy .QBE. However, relations, filters, calculated fields and any other parameters set in the calling code will not be reflected in the generated .REP or .LAB file.

Many reports will require some hand-coding to fully convert to Visual dBASE 7, especially to the new Data classes. The SQL set-oriented syntax used by the Data classes is inherently incompatible with the record-oriented syntax of earlier versions of dBASE. The Wizard will provide the basic objects you need to access the data from the original Crystal report. However there’s no way the Wizard can parse the entire dBASE Data Manipulation Language (not to mention your own User-Defined Functions) to map your code to an equivalent SQL statement. For more information on converting data, see One-To-Many Reports.


How To Use This Wizard

Converting reports from Crystal 3.0 to Visual dBASE 7 Report classes is a simple two-step process:

1	Select “Report” or “Label” to indicate the type of file to be converted.

2	Identify the Crystal file to be converted and the path and name of the Visual dBASE 7 Report file to be generated.

Click “Next” and “Finish” and the Report Converter Wizard will generate a .REP or .LAB file depending on whether you selected “Report” or “Label”.

Preparing For Conversion

The Visual dBASE 7 Report Converter Wizard will read your Crystal 3.0 Report file regardless of the folder in which it’s stored at the time of the conversion. However, the same is not true for Queries. In order to establish one-to-many relations between Query objects, the Wizard must run the .QBE file associated with your Crystal Report. If the tables are not in the same folder and there’s no path to the tables called out in the .QBE file, the relations will not be established, nor will the order in which data is displayed.

Make sure your .QBE files have explicit paths to open tables. The Report Converter Wizard cannot execute a SET PATH statement.

If the .QBE fails to open, the Wizard will still generate the Visual dBASE 7 report. However, the report will not have any order specified for the data nor will there be any relations between Queries.

Source

Enter the path and filename of the Crystal 3.0 Report or Label file to be converted. Click the “tool” button next to the field to browse.

Target

Enter the path and filename for the new Visual dBASE 7 generated .REP file. Click the “tool” button next to the field to browse.

Report or Label

Click on Report or Label to indicate the type of file to be converted.


One-To-Many Reports

One-To-Many Reports in Visual dBASE 7 are somewhat complicated by the nature of the SQL-based Query classes. Earlier versions of dBASE supported all types of expressions in SET RELATION statements as well as Referential Integrity, Constraints and other factors now supported at the engine level. In addition, the increased Client/Server emphasis of Visual dBASE 7 means that databases, tables and indexes may have to be rethought or redesigned to derive the greatest benefit from the new classes. Keep this in mind when you convert reports. It’s probably wise to plan to adjust the relations between tables in one-to-many reports generated by this Wizard.

There are three ways to relate two or more tables in Visual dBASE 7: joins, masterSource and masterRowset. The approach you use will be determined by the nature of the relationship between tables weighed against scalability and performance issues. Simple single-field relations between tables are handled most easily using masterSource, which doesn’t support multi-field or expression relations as easily. masterRowset is faster for DBF and DB tables, but can be slower for remote RDBMS engines like Oracle, Interbase or Microsoft/Sybase SQL-Server. You should perform some experiments with your generated Visual dBASE 7 reports and labels to determine the most effective means of relating data

Joins

The SQL Select statement in Visual dBASE 7 Query classes supports joins - the combination of fields from more than one table into a single rowset. Joins generate, in effect, a temporary table combining the fields selected from multiple tables. The resulting rowset can be viewed in a banded one-to-many report using the Group class, much as Groups worked in Crystal and Group By worked in earlier versions of dBASE. When a key field changes, a new group is started. Joins can be slower than other one-to-many approaches when you have indexes appropriate to the relationship between tables available. However, joins can be extremely effective when gathering together large quantities of unstructured data.

masterSource

The Visual dBASE 7 Report Converter uses masterSource as its default implementation of one-to-many reports. Similar in effect to SET FILTER, it doesn’t require an index on the key field that connects the two tables (though performance can be significantly enhanced if an index is available). In essence, the child table is re-queried each time the key field changes in the parent table. It accomplishes this using a host variable. Visual dBASE 7 automatically updates a variable matching the key field in the parent query. The host variable is used in the SQL SELECT statement of the child query. Each time the host variable’s value changes, the child table is re-queried.

The relation between parent and child queries is set up entirely in the child using the masterSource and sql properties. The masterSource of the child query points to the parent query’s rowset. The sql property of the child query is an SQL SELECT statement that references the key field of the parent as a variable, for example:

select * from "BOOKS" where LEAD_AUTH = :AUTHOR_ID

“:AUTHOR_ID” is the host variable containing the value of the Author_id field in the current row of the parent query. Lead_auth is corresponding field in the child query.

masterRowset

Where appropriate index keys exist, masterRowset can be the high-performance solution to one-to-many reports. masterRowset is used in combination with masterFields and is similar to the legacy command SET KEY TO... CONSTRAIN. It uses indexes to automatically select a subset of rows when the parent rowset key changes. There’s an important distinction between masterSource and masterRowset. masterSource addresses the table itself, re-querying as needed. masterRowset, on the other hand, never requeries the table - it works with the existing parent and child rowsets. Therefore, you must be sure to select all records that might be applicable to your report in your SQL SELECT statement lest table records be excluded from the report because they’re not in the child query’s rowset.

If you’re using dBASE tables, this is the most highly optimized approach to one-to-many.

To implement masterRowset, start with an SQL SELECT statement in your sql property similar to:

select * from "MYTABLE"

which opens the table and selects all records. Then relate the resulting rowset of the parent query to the rowset of the child query by pointing to the parent rowset in the masterRowset property of the child. Use the masterFields property of the child query to indicate the field or fields in the parent rowset that will key into your index.

Notes

The child table must be indexed on the field or fields indicated in the masterFields property.

The child query’s rowset's indexName property must point to the index tag that matches the masterFields property.

masterRowset is the ideal solution for multiple relations to a single parent, especially in dBASE tables.

Depending on optimization, indexes and other subtle factors, masterRowset/masterFields may not yield the best results when using remote RDBMS engines and cannot be used in unstructured relations, where appropriate indexes are not available.

Summary

Visual dBASE 7 provides a wealth of options in optimizing one-to-many reports. Too many, in fact, for the Report Converter Wizard to manage with the limited information available in the original Crystal Reports file. Therefore, one-to-many support in the Wizard is limited to the simplest single-field relations using masterRowset.

For more detailed information on Data Classes, Report Classes and relations between queries, see the Visual dBASE 7 Developer’s Guide and Online Help.